package edu.hawaii.server;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import edu.hawaii.client.AvailableSeatsBySection;
import edu.hawaii.client.CourseNameByCampus;
import edu.hawaii.client.Semester;
import edu.hawaii.client.SubjectFilteredByAttributes;

/**
 * Establishes database connection then calls stored procedures and returns results as Java Bean 
 * Objects.  Each method is for a specific stored procedure call.
 *  
 *  Classes interactions:
 *    - ObjectFactory.java to make java Bean objects from results from database.
 *    - DatabaseConnector.java used to make the connection to specific database with in class. 
 * 
 * @author Kiet Huynh
 * @author brandon lee
 */
public class CallingStoredProcedures {
  
  /**
   * Calls the stored procedure "ReportServer_ShortCourseNamesBySemesterAndCampus_proc"
   * and returns the course names as the Java bean CourseNameByCampus objects.  
   * The subjects returned depending on the selected campus and semester.
   * 
   * @param campus The name of the campus at which the courses are offered. It consists
   *        of the first 3 characters of the campus. For example, MAN for Manoa, HON for 
   *        Honolulu Community Colloge, etc...
   * @param semester The semester in which the courses are offered.  It consists of 6 
   *        digits.  The first 4 digits are the year, and the last 2 are the semester.
   *        For example, 200730 is the Spring 2007.
   * @return A list of CourseNameByCampus objects.
   */
   public static List callCourseNameByCampusSP(String campus, String semester) {
     Connection conn = null;
     CallableStatement cstmt = null;
     ResultSet rs = null;
      
    try {
      // Establish the connection
      conn = DatabaseConnector.getConnection();

      // pass parameter to the
      // "dbo.ReportServer_EnrollmentManagement_ComboBox_Coursename_proc"
      // stored procedure. 
      cstmt = conn.prepareCall("" + 
          "{call dbo.ReportServer_ShortCourseNamesBySemesterAndCampus_proc(?, ?)}");

      cstmt.setString(1, campus); // MAN
      cstmt.setString(2, semester); // 200710
   
      // execute the stored procudure
      rs = cstmt.executeQuery();
      return ObjectFactory.convertsToObjects(rs, CourseNameByCampus.class);
    }
    
    catch (Exception e) {
      e.printStackTrace();
      return new ArrayList();
    }
    
    finally {
      if (rs != null) {
        try {
          rs.close();
        }
        catch (Exception e) {
          //
        }
      }

      if (cstmt != null) {
        try {
          cstmt.close();
        }
        catch (Exception e) {
          //
        }
      }

      if (conn != null) {
        try {
          conn.close();
        }
        catch (Exception e) {
          //
        }
      }
    }
  }
  
  /**
   * Executes the stored procudure 
   * "dbo.ReportServer_EnrollmentManagement_ViewAvailableSeatsBySection_proc"
   * and returns the results as a list of AvailableSeatsBySection objects.
   * It fullfils the search for available seats by section.
   * 
   * @param levelOfCourse All courses under this level will be returned.  For example, 
   *        if it is 500, all undergraduate courses will be returned.
   * @param campus The name of the campus at which the courses are offered. It consists
   *        of the first 3 characters of the campus. For example, MAN for Manoa, HON for 
   *        Honolulu Community Colloge, etc...
   * @param semester The semester in which the courses are offered.  It consists of 6 
   *        digits.  The first 4 digits are the year, and the last 2 are the semester.
   *        For example, 200730 is the Spring 2007.
   * @param school The school in which the courses are offered. It consists of 2 digits. 
   *        Use % for the wildcard. For example, 10 is for Arts and Science.
   * @param maxCurrentEnrollment The maximum current enrollment in a course.  For example,
   *        if 15 is entered, all the courses that have current enrollment of 15 or less 
   *        will be returned. 
   * @param courseName  The name of a course. It uses the University of Hawaii naming 
   *        convention. User % for the wildcard. For example, ICS for Information 
   *        Computer Science. 
   * @param division Not sure at this moment. Use % for the wildcard.
   * @return A list of AvailableSeatsBySectionObjects which represent records returned 
   * by the stored procedures.
   */
   public static List callAvailableSeatsBySectionSP(String levelOfCourse, String campus, 
                String semester, String school, int maxCurrentEnrollment, 
                String courseName, String division) {
    
     Connection conn = null;
     CallableStatement cstmt = null;
     ResultSet rs = null;
 
    try {
      // Establish the connection
      conn = DatabaseConnector.getConnection();
      
      // pass parameter to the 
      // "dbo.ReportServer_EnrollmentManagement_ViewAvailableSeatsBySection_proc" 
      // stored procedure. For security reasons,
      cstmt = conn.prepareCall("" +
          "{call dbo.ReportServer_EnrollmentManagement_ViewAvailableSeatsBySection_proc(?, "
          + "?, ?, ?, ?, ?, ?)}");
      
      cstmt.setString(1, levelOfCourse); // 500
      cstmt.setString(2, campus); // MAN
      cstmt.setString(3, semester); // 200730
      cstmt.setString(4, school); // 10
      cstmt.setInt(5, maxCurrentEnrollment); // 1000
      cstmt.setString(6, courseName); // ICS 
      cstmt.setString(7, division); // %
     
      
      // execute the stored procudure
      rs = cstmt.executeQuery();
      return ObjectFactory.convertsToObjectsASBS(rs, AvailableSeatsBySection.class);
    }
    
    catch (Exception e) {
      e.printStackTrace();
      return new ArrayList();
    }
    
    finally {
      if (rs != null) {
        try {
          rs.close();
        }
        catch (Exception e) {
          //
        }
      }
      
      if (cstmt != null) {
        try {
          cstmt.close();
        }
        catch (Exception e) {
          //
        }
      }
      
      if (conn != null) {
        try {
          conn.close();
        }
        catch (Exception e) {
          //
        }
      }    
    }
  } 
  
   /**
    * Calls the stored procudure dbo.ReportServer_Semester_proc to get 
    * the semester as text and the semester key.
    * @param campus The first 3 letters of the campus name.  For example, MAN for Manoa, HON 
    * for Honolulu Community College.
    * @return A list of Semester object.
    */
  public static List callSemesterSP(String campus) {

    Connection conn = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    
    try {
      // Establish the connection
      conn = DatabaseConnector.getConnection();

      // pass parameter to the
      // "dbo.ReportServer_EnrollmentManagement_ViewAvailableSeatsBySection_proc"
      // stored procedure. For security reasons,
      cstmt = conn.prepareCall("{call dbo.ReportServer_Semester_proc(?)}");
      
      cstmt.setString(1, campus);

      // execute the stored procudure
      rs = cstmt.executeQuery();
      return ObjectFactory.convertsToObjects(rs, Semester.class);
    }
    catch (Exception e) {
      e.printStackTrace();
      return new ArrayList();
    }
    finally {
      if (rs != null) {
        try {
          rs.close();
        }
        catch (Exception e) {
          //
        }
      }

      if (cstmt != null) {
        try {
          cstmt.close();
        }
        catch (Exception e) {
          //
        }
      }

      if (conn != null) {
        try {
          conn.close();
        }
        catch (Exception e) {
          //
        }
      }
    }
  }
  
  /**
   * Calls the stored procedure "ReportServer_SubjectsFilteredByAttributes_proc"
   * and returns the course names as the Java bean CourseNameByCampus objects.  
   * The course names returned depend on the selected campus, semester, and attributes.
   * 
   * @param campus The name of the campus at which the courses are offered. It consists
   *        of the first 3 characters of the campus. For example, MAN for Manoa, HON for 
   *        Honolulu Community Colloge, etc...
   * @param semester The semester in which the courses are offered.  It consists of 6 
   *        digits.  The first 4 digits are the year, and the last 2 are the semester.
   *        For example, 200730 is the Spring 2007.
   * @param attributes The attributes of a course such as DS, DB, WI, OC.
   * @return A list of SubjectFilteredByAttributes objects.
   */
   public static List callSubjectsFilteredByAttributesSP(String campus, String semester, 
       String attributes) {
     
     Connection conn = null;
     CallableStatement cstmt = null;
     ResultSet rs = null;
      
    try {
      // Establish the connection
      conn = DatabaseConnector.getConnection();

      // pass parameter to the
      // "dbo.ReportServer_EnrollmentManagement_ComboBox_Coursename_proc"
      // stored procedure. 
      cstmt = conn.prepareCall("" + 
          "{call dbo.ReportServer_SubjectsFilteredByAttributes_proc(?, ?, ?)}");

      cstmt.setString(1, campus); // MAN
      cstmt.setString(2, semester); // 200730
      cstmt.setString(3, attributes); // OC
   
      // execute the stored procudure
      rs = cstmt.executeQuery();
      return ObjectFactory.convertsToObjects(rs, SubjectFilteredByAttributes.class);
    }
    
    catch (Exception e) {
      e.printStackTrace();
      return new ArrayList();
    }
    
    finally {
      if (rs != null) {
        try {
          rs.close();
        }
        catch (Exception e) {
          //
        }
      }

      if (cstmt != null) {
        try {
          cstmt.close();
        }
        catch (Exception e) {
          //
        }
      }

      if (conn != null) {
        try {
          conn.close();
        }
        catch (Exception e) {
          //
        }
      }
    }
  }
   
   /**
    * Executes the stored procudure 
    * "dbo.ReportServer_SearchCoursesByAttributes_proc"
    * and returns the results as a list of AvailableSeatsBySection objects.
    * 
    * @param levelOfCourse All courses under this level will be returned.  For example, 
    *        if it is 500, all undergraduate courses will be returned.
    * @param campus The name of the campus at which the courses are offered. It consists
    *        of the first 3 characters of the campus. For example, MAN for Manoa, HON for 
    *        Honolulu Community Colloge, etc...
    * @param semester The semester in which the courses are offered.  It consists of 6 
    *        digits.  The first 4 digits are the year, and the last 2 are the semester.
    *        For example, 200730 is the Spring 2007.
    * @param school The school in which the courses are offered. It consists of 2 digits. 
    *        Use % for the wildcard. For example, 10 is for Arts and Science.
    * @param maxCurrentEnrollment The maximum current enrollment in a course.  For example,
    *        if 15 is entered, all the courses that have current enrollment of 15 or less 
    *        will be returned. 
    * @param courseName  The name of a course. It uses the University of Hawaii naming 
    *        convention. User % for the wildcard. For example, ICS for Information 
    *        Computer Science. 
    * @param division Not sure at this moment. Use % for the wildcard.
    * @param attribute The attributes of a course such as DS, DB, WI, OC.
    * @return A list of AvailableSeatsBySectionObjects which represent records returned 
    * by the stored procedures.
    */
    public static List callSearchCoursesByAttributesSP(String levelOfCourse, String campus, 
                 String semester, String school, int maxCurrentEnrollment, 
                 String courseName, String division, String attribute) {
     
      Connection conn = null;
      CallableStatement cstmt = null;
      ResultSet rs = null;
  
     try {
       // Establish the connection
       conn = DatabaseConnector.getConnection();
       
       // pass parameter to the 
       // "dbo.ReportServer_EnrollmentManagement_ViewAvailableSeatsBySection_proc" 
       // stored procedure. For security reasons,
       cstmt = conn.prepareCall("" +
           "{call dbo.ReportServer_EnrollmentManagement_ViewAvailableSeatsBySection_proc(?, "
           + "?, ?, ?, ?, ?, ?, ?)}");
       
       cstmt.setString(1, levelOfCourse); // 500
       cstmt.setString(2, campus); // MAN
       cstmt.setString(3, semester); // 200730
       cstmt.setString(4, school); // 10
       cstmt.setInt(5, maxCurrentEnrollment); // 1000
       cstmt.setString(6, courseName); // ICS 
       cstmt.setString(7, division); // %
       cstmt.setString(8, attribute);
       
       // execute the stored procudure
       rs = cstmt.executeQuery();
       return ObjectFactory.convertsToObjectsASBS(rs, AvailableSeatsBySection.class);
     }
     
     catch (Exception e) {
       e.printStackTrace();
       return new ArrayList();
     }
     
     finally {
       if (rs != null) {
         try {
           rs.close();
         }
         catch (Exception e) {
           //
         }
       }
       
       if (cstmt != null) {
         try {
           cstmt.close();
         }
         catch (Exception e) {
           //
         }
       }
       
       if (conn != null) {
         try {
           conn.close();
         }
         catch (Exception e) {
           //
         }
       }    
     }
   }  
}
